﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.StorageClient;
using System.IO;
using System.Web.Security;
using System.Xml;
using System.ServiceModel.Channels;
using System.Threading.Tasks;
using System.Data;
using System.Collections;

namespace WebRole1
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    public class Service1 : IService1
    {
        public System.ServiceModel.Channels.Message ProvidePolicyFile()
        {
            FileStream filestream = File.Open(@"ClientAcessPolicy.xml", FileMode.Open);
            // Either specify ClientAcessPolicy.xml file path properly           
            // or put that in \Bin folder of the console application            
            XmlReader reader = XmlReader.Create(filestream);
            System.ServiceModel.Channels.Message result = System.ServiceModel.Channels.Message.CreateMessage(MessageVersion.None, "", reader);
            return result;
        }

        # region SQL common methods

        public static string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
        public SqlConnection conn = null;
        private SqlCommand SqlCommandConnection()
        {
            SqlCommand sql = new SqlCommand();
            sql.Connection = conn;
            return sql;
        }
        private void SqlOpenConnection()
        {
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = conString;
                conn.Open();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception)
            {
                SqlConnection.ClearAllPools();
            }
        }

        private void CloseConnection()
        {
            conn.Close();
        }

        # endregion

        public string login(string username, string password)
        {
            SqlOpenConnection();
            string name = "";
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select name from UserAccount where email=@username AND password=@password";
            comm.Parameters.AddWithValue("@username", username);
            comm.Parameters.AddWithValue("@password", hashPassword(password));

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                name = (string)(dr["name"]);
            }
            dr.Close(); dr.Dispose();

            CloseConnection();
            return name;
        }

        public List<string> retrieveRoles(string username)
        {
            List<string> result = new List<string>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT userType FROM user_roles, roles WHERE user_roles.roles = roles.uid AND user_roles.userAccount = @username";
            comm.Parameters.AddWithValue("@username", username);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result.Add(dr["userType"].ToString());

            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        protected static string hashPassword(string password)
        {
            string result = "";
            result = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
            return result;
        }

        # region image
        // !!!!!!!!!!!!!!!!!!!!!! READ ME!!!!!!!!!!!!!!!!!!!!!!!!
        // for your benefit, this url http://erss.blob.core.windows.net/mycontainer/167054_10150090407224445_655129444_5858965_8253989_n.jpg
        // has already an image in it
        //
        //
        // if you tried to received image but have maxReceivedMessageSize exceeded, go to
        // app.config in your WCF app
        // and set something like maxReceivedMessageSize="20000000"
        // 
        // cheers
        /// <summary>
        /// retrieve all the uri of the items in storage
        /// </summary>
        /// <returns></returns>
        public string retrieveItemsList()
        {
            string result = "";
            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference("mycontainer");
            // Browse through blob list from the container
            IEnumerable<IListBlobItem> objBlobList = objContainer.ListBlobs();
            foreach (IListBlobItem objItem in objBlobList)
            {
                result += objItem.Uri + "<br>";
            }
            return result;
        }

        /// <summary>
        /// Upload Image into the Blob
        /// </summary>
        /// <param name="fileName">FileUpload1.FileName</param>
        /// <param name="fileBytes">FileUpload1.FileBytes</param>
        /// <returns>container/filename</returns>
        public string uploadItem(string fileName, byte[] fileBytes)
        {

            bool result = false;
            try
            {
                CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
                {
                    configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
                });
                // Get the storage account reference
                CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
                // get the Client reference using storage blobend point
                CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
                // Get Container reference
                CloudBlobContainer objContainer = objClient.GetContainerReference("mycontainer");
                // Get blob reference
                CloudBlob obj = objContainer.GetBlobReference(fileName);
                // Set meta values
                obj.Metadata["MetaName"] = "meta";
                // Open a stream using the cloud object
                BlobStream blobstream = obj.OpenWrite();
                // Write the stream to the blob database
                blobstream.Write(fileBytes, 0, fileBytes.Count());
                blobstream.Close();
                result = true;
            }
            catch (Exception)
            {
                result = false;
            }
            return result == true ? "http://erss.blob.core.windows.net/mycontainer/" + fileName : String.Empty;


        }

        /// <summary>
        /// Upload Image into the Blob
        /// </summary>
        /// <param name="fileName">FileUpload1.FileName</param>
        /// <param name="fileBytes">FileUpload1.FileBytes</param>
        /// <returns>container/filename</returns>
        public string uploadFaceImage(string fileName, byte[] fileBytes)
        {

            bool result = false;
            try
            {
                CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
                {
                    configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
                });
                // Get the storage account reference
                CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
                // get the Client reference using storage blobend point
                CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
                // Get Container reference
                // If i set to face, i can't retrieve it T>T
                //CloudBlobContainer objContainer = objClient.GetContainerReference("face");
                CloudBlobContainer objContainer = objClient.GetContainerReference("mycontainer");
                // Get blob reference
                CloudBlob obj = objContainer.GetBlobReference(fileName);
                // Set meta values
                obj.Metadata["MetaName"] = "meta";
                // Open a stream using the cloud object
                BlobStream blobstream = obj.OpenWrite();
                // Write the stream to the blob database
                blobstream.Write(fileBytes, 0, fileBytes.Count());
                blobstream.Close();
                result = true;
            }
            catch (Exception)
            {
                result = false;
            }
            return result == true ? "http://erss.blob.core.windows.net/mycontainer/" + fileName : String.Empty;


        }

        /// <summary>
        /// retrieve search the Image in mycontainer
        /// </summary>
        /// <param name="txtSearch">Name of Image Example asd.jpg</param>
        /// <returns>System.Drawing.Image</returns>
        public byte[] searchAndretrieveImage(string txtSearch)
        {
            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference("mycontainer");


            // Get the blob reference using the blob name provided in the search
            CloudBlob obj = objContainer.GetBlobReference(txtSearch);
            BlobStream blobstream = obj.OpenRead();

            // Create the image object and display the same on the browser response
            System.Drawing.Image objimg = null;

            try
            {
                objimg = System.Drawing.Image.FromStream(blobstream, true);

                //convert from Drawing.Image to byte[]
                MemoryStream ms = new MemoryStream();
                objimg.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                byte[] asd = ms.ToArray();


                //// create a bitmapimage from byte[] 
                //// example only
                //BitmapImage bi = new BitmapImage();
                //bi.BeginInit();
                //bi.StreamSource = new MemoryStream(asd);
                //bi.EndInit();


                return asd;
            }
            catch (Exception)
            {
                return null;
            }

        }

        /// <summary>
        /// retrieve Image base on URL
        /// </summary>
        /// <param name="txtSearch">Name of Image Example asd.jpg</param>
        /// <returns>System.Drawing.Image</returns>
        public byte[] retrieveImage(string URL)
        {
            string txtSearch = "";
            string container = "";
            //char[] delimiters = new char[] { '\r', '\n' };
            string[] parts = URL.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < parts.Length; i++)
            {

                if (parts[i] == "face")
                {
                    container = "face";
                }
                else if (parts[i] == "mycontainer")
                {
                    container = "mycontainer";
                }
                else if (parts[i] != "http:" && parts[i] != "erss.blob.core.windows.net")
                {
                    txtSearch = parts[i];
                }
            }

            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference(container);



            // Get the blob reference using the blob name provided in the search
            CloudBlob obj = objContainer.GetBlobReference(txtSearch);
            BlobStream blobstream = obj.OpenRead();

            // Create the image object and display the same on the browser response
            System.Drawing.Image objimg = null;

            try
            {
                objimg = System.Drawing.Image.FromStream(blobstream, true);

                //convert from Drawing.Image to byte[]
                MemoryStream ms = new MemoryStream();
                objimg.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                byte[] asd = ms.ToArray();


                //// create a bitmapimage from byte[] 
                //// example only
                //BitmapImage bi = new BitmapImage();
                //bi.BeginInit();
                //bi.StreamSource = new MemoryStream(asd);
                //bi.EndInit();


                return asd;
            }
            catch (Exception)
            {
                return null;
            }


        }



        /// <summary>
        /// search for a item
        /// </summary>
        /// <param name="txtSearch">fileName to search</param>
        /// <returns>byte[]</returns>
        public BlobStream searchAndretrieveItem(string txtSearch)
        {
            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference("mycontainer");


            // Get the blob reference using the blob name provided in the search
            CloudBlob obj = objContainer.GetBlobReference(txtSearch);
            BlobStream blobstream = obj.OpenRead();

            return blobstream;

        }

        /// <summary>
        /// retrieve an item base on the URL
        /// </summary>
        /// <param name="txtSearch">URL</param>
        /// <returns>byte[]</returns>
        public BlobStream retrieveItem(string URL)
        {
            string txtSearch = "";
            string container = "";
            //char[] delimiters = new char[] { '\r', '\n' };
            string[] parts = URL.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < parts.Length; i++)
            {

                if (parts[i] == "face")
                {
                    container = "face";
                }
                else if (parts[i] == "mycontainer")
                {
                    container = "mycontainer";
                }
                else if (parts[i] != "http:" && parts[i] != "erss.blob.core.windows.net")
                {
                    txtSearch = parts[i];
                }
            }

            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference(container);



            // Get the blob reference using the blob name provided in the search
            CloudBlob obj = objContainer.GetBlobReference(txtSearch);
            BlobStream blobstream = obj.OpenRead();
            return blobstream;

        }

        # endregion

        # region CRUD for table patient_faceImg

        public bool createPatient_faceImg(string patient, string path)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO patient_faceImg Values (@patient, @path)";
            comm.Parameters.AddWithValue("@patient", patient);
            comm.Parameters.AddWithValue("@path", path);
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        /// <summary>
        /// retrieve the URL for faceImg
        /// </summary>
        /// <param name="patient">uid of patient</param>
        /// <returns>a list of path</returns>
        public List<string> retrievePatient_faceImg(string patient)
        {
            List<string> result = new List<string>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT faceImgPath FROM  patient_faceImg WHERE patient = @patient";
            comm.Parameters.AddWithValue("@patient", patient);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result.Add(dr["faceImgPath"].ToString());
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        # endregion

        # region CRUD for exe

        public bool createEXE(string name, string path, string desc, int exeType, List<string> custKey)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO exe Values (@name, @path, @desc,@exeType)";
            comm.Parameters.AddWithValue("@name", name);
            comm.Parameters.AddWithValue("@path", path);
            comm.Parameters.AddWithValue("@desc", desc);
            comm.Parameters.AddWithValue("@exeType", exeType);
            int result = comm.ExecuteNonQuery();
            CloseConnection();

            // create_exe_custKey

            return result == 0 ? false : true;
        }

        public List<EXE> retrieveAllEXE()
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = " SELECT exe.uid, exe.name,exe.path,exe.description,exe.exeType,exe.description FROM  exe, exeType WHERE exe.exeType = exeType.uid";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                EXE p = new EXE();
                p.Description = dr["description"].ToString();
                p.Name = dr["name"].ToString();
                p.Path = dr["path"].ToString();
                try
                {
                    p.ExeType = Convert.ToInt16(dr["exeType"].ToString());
                    p.ExeTypedescription = dr["exeTypedescription"].ToString();
                }
                catch (Exception) { }

                p.Uid = Convert.ToInt16(dr["uid"].ToString());

                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }

            return result;
        }

        public List<EXE> retrieveAllEXEofExeType(int exeType)
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  exe";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                if (Convert.ToInt16(dr["exeType"].ToString()) == exeType)
                {
                    EXE p = new EXE();
                    p.Description = dr["description"].ToString();
                    p.Name = dr["name"].ToString();
                    p.Path = dr["path"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.ExeType = Convert.ToInt16(dr["exeType"].ToString());
                        p.ExeTypedescription = dr["exeTypedescription"].ToString();
                    }
                    catch (Exception) { }

                    result.Add(p);
                }
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }

            return result;
        }

        public List<EXE> retrieveAllExeOfExeType(int exeType, int joint)
        {
            List<EXE> result = retrieveAllEXEofExeType(exeType);
            foreach (EXE exe in result)
            {
                if (Convert.ToInt16(exe.Tag) != joint)
                {
                    result.Remove(exe);
                }
            }
            return result;
        }

        public EXE retrieveEXE(int exeID)
        {
            EXE result = new EXE();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  exe WHERE uid = @exeID";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result.Description = dr["description"].ToString();
                result.Name = dr["name"].ToString();
                result.Path = dr["path"].ToString();
                result.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    result.ExeType = Convert.ToInt16(dr["exeType"].ToString());
                    result.ExeTypedescription = dr["exeTypedescription"].ToString();
                }
                catch (Exception) { }

            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            result.Tag = retrieveTag(result.Uid);
            result.ExeStage = retrieveExeStage(result.Uid);
            result.CustKey = retrieve_exe_custKey(result.Uid);
            return result;
        }

        public List<string> retrieveTag(int exeID)
        {
            List<string> result = new List<string>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT joint FROM  exe_tag WHERE exeID = @exeID";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                string temp = dr["joint"].ToString();
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve all exe id that belongs to that tag
        /// </summary>
        /// <param name="joint"></param>
        /// <returns></returns>
        public List<int> retrieveExeIDofTag(int joint)
        {
            List<int> result = new List<int>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exeID FROM  exe_tag WHERE joint = @joint";
            comm.Parameters.AddWithValue("@joint", joint);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                int temp = Convert.ToInt16(dr["exeID"].ToString());
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve all exe id that belongs to that tag
        /// </summary>
        /// <param name="joint"></param>
        /// <returns></returns>
        public List<EXE> retrieveExeofTag(int joint)
        {
            List<int> listExeID = new List<int>();
            listExeID = this.retrieveExeIDofTag(joint);
            List<EXE> result = new List<EXE>();
            foreach (int i in listExeID)
            {
                result.Add(this.retrieveEXE(i));
            }
            return result;
        }


        # endregion

        //need to change
        #region CRUD for problem

        public bool updateProblem(int uid, int patientID, string joint, string problems, string description, int severity, string notes)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "UPDATE problem SET patientID = @patientID, joint = @joint, problems = @problems, description = @description, severity = @severity, notes = @notes WHERE uid = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@joint", joint);
            comm.Parameters.AddWithValue("@problems", problems);
            comm.Parameters.AddWithValue("@description", description);
            comm.Parameters.AddWithValue("@severity", severity);
            comm.Parameters.AddWithValue("@notes", notes);
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        /// <summary>
        /// update problem specifically for description, severity and notes
        /// </summary>
        /// <param name="joint"></param>
        /// <param name="patientID"></param>
        /// <param name="description"></param>
        /// <param name="severity"></param>
        /// <param name="notes"></param>
        /// <returns></returns>
        public bool updateProblem1(int joint, int patientID, string description, int severity, string notes)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "UPDATE problem SET description = @description, severity = @severity, notes = @notes WHERE joint = @joint AND patientID = @patientID";
            comm.Parameters.AddWithValue("@joint", joint);
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@description", description);
            comm.Parameters.AddWithValue("@severity", severity);
            comm.Parameters.AddWithValue("@notes", notes);
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        public bool updateProblem2(int joint, int patientID, string description, string notes)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "UPDATE problem SET description = @description, notes = @notes WHERE joint = @joint AND patientID = @patientID";
            comm.Parameters.AddWithValue("@joint", joint);
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@description", description);
            comm.Parameters.AddWithValue("@notes", notes);
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        public bool createProblem(int patientID, string joint, string problems, string description, int severity, string notes)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO problem Values (@patientID,@joint,@problems, @description, @severity, @notes)";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@joint", joint);
            comm.Parameters.AddWithValue("@problems", problems);
            comm.Parameters.AddWithValue("@description", description);
            comm.Parameters.AddWithValue("@severity", severity);
            comm.Parameters.AddWithValue("@notes", notes);
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        public List<problem> retrievePatientProblems(int patientID)
        {
            List<problem> result = new List<problem>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  problem, tag WHERE problem.patientID = @patientID AND tag.joint = problem.joint ";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                problem p = new problem();
                p.Description = dr["description"].ToString();
                p.Joint = dr["joint"].ToString();
                p.Notes = dr["notes"].ToString();
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.Problems = dr["problems"].ToString();
                p.Severity = Convert.ToInt16(dr["severity"].ToString());
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                p.JointName = dr["jointName"].ToString();
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        public bool deleteProblems(int uid)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "DELETE FROM problem WHERE uid = @uid ";
            comm.Parameters.AddWithValue("@uid", uid);
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        # endregion

        #region CRUD for patient

        public int addNewPatient(string lastName, string firstName, string title, DateTime dOB, string gender, string country, string address1, string address2, string postalCode, string homePhone, string workPhone, string mobile, string email, string doctor, string NOKName, string NOKAddress1, string NOKAddress2, string NOKPostalCode, string NOKphoneNumber)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO patient (LastName, FirstName, title, dateOfBirth, gender, country, address1, address2, postcode, contactNumHome, contactNumWork, contactNum, email, doctor, e_name, e_address1, e_address2, e_postalcode, e_contactNum) Values (@lastName, @firstName, @title, @dOB, @gender, @country, @address1, @address2, @postalCode, @homePhone, @workPhone, @mobile, @email, @doctor, @NOKName, @NOKAddress1, @NOKAddress2, @NOKPostalCode, @NOKphoneNumber);" + "Select Scope_Identity()";
            comm.Parameters.AddWithValue("@lastName", lastName);
            comm.Parameters.AddWithValue("@firstName", firstName);
            comm.Parameters.AddWithValue("@title", title);
            comm.Parameters.AddWithValue("@dOB", dOB);
            comm.Parameters.AddWithValue("@gender", gender.Substring(0, 1));
            comm.Parameters.AddWithValue("@country", country);
            comm.Parameters.AddWithValue("@address1", address1);
            comm.Parameters.AddWithValue("@address2", address2);
            comm.Parameters.AddWithValue("@postalCode", postalCode);
            comm.Parameters.AddWithValue("@homePhone", homePhone);
            comm.Parameters.AddWithValue("@workPhone", workPhone);
            comm.Parameters.AddWithValue("@mobile", mobile);
            comm.Parameters.AddWithValue("@email", email);
            comm.Parameters.AddWithValue("@doctor", doctor);
            comm.Parameters.AddWithValue("@NOKName", NOKName);
            comm.Parameters.AddWithValue("@NOKAddress1", NOKAddress1);
            comm.Parameters.AddWithValue("@NOKAddress2", NOKAddress2);
            comm.Parameters.AddWithValue("@NOKPostalCode", NOKPostalCode);
            comm.Parameters.AddWithValue("@NOKphoneNumber", NOKphoneNumber);

            int id = Convert.ToInt32(comm.ExecuteScalar());
            CloseConnection();
            return id;// == 0 ? false : true;
        }

        public int addNewAPatient(string lastName, string firstName, string title,string gender, string country, string address1, string address2, string postalCode, string homePhone, string workPhone, string mobile, string email, string doctor, string NOKName, string NOKAddress1, string NOKAddress2, string NOKPostalCode, string NOKphoneNumber)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO patient (LastName, FirstName, title,gender, country, address1, address2, postcode, contactNumHome, contactNumWork, contactNum, email, doctor, e_name, e_address1, e_address2, e_postalcode, e_contactNum) Values (@lastName, @firstName, @title, @gender, @country, @address1, @address2, @postalCode, @homePhone, @workPhone, @mobile, @email, @doctor, @NOKName, @NOKAddress1, @NOKAddress2, @NOKPostalCode, @NOKphoneNumber);" + "Select Scope_Identity()";
            comm.Parameters.AddWithValue("@lastName", lastName);
            comm.Parameters.AddWithValue("@firstName", firstName);
            comm.Parameters.AddWithValue("@title", title);
            //comm.Parameters.AddWithValue("@dOB", dOB);
            comm.Parameters.AddWithValue("@gender", gender.Substring(0, 1));
            comm.Parameters.AddWithValue("@country", country);
            comm.Parameters.AddWithValue("@address1", address1);
            comm.Parameters.AddWithValue("@address2", address2);
            comm.Parameters.AddWithValue("@postalCode", postalCode);
            comm.Parameters.AddWithValue("@homePhone", homePhone);
            comm.Parameters.AddWithValue("@workPhone", workPhone);
            comm.Parameters.AddWithValue("@mobile", mobile);
            comm.Parameters.AddWithValue("@email", email);
            comm.Parameters.AddWithValue("@doctor", doctor);
            comm.Parameters.AddWithValue("@NOKName", NOKName);
            comm.Parameters.AddWithValue("@NOKAddress1", NOKAddress1);
            comm.Parameters.AddWithValue("@NOKAddress2", NOKAddress2);
            comm.Parameters.AddWithValue("@NOKPostalCode", NOKPostalCode);
            comm.Parameters.AddWithValue("@NOKphoneNumber", NOKphoneNumber);

            int id = Convert.ToInt32(comm.ExecuteScalar());
            CloseConnection();
            return id;// == 0 ? false : true;
        }

        public List<patient> retrieveAllOwnPatients(string docname)
        {
            List<patient> result = new List<patient>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM patient where doctor = @doc";
                comm.Parameters.AddWithValue("@doc", docname);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    patient p = new patient();
                    p.Address1 = dr["Address1"].ToString();
                    p.Address2 = dr["Address2"].ToString();
                    p.ContactNum = dr["contactNum"].ToString();
                    p.Doc = dr["doctor"].ToString();
                    p.E_contactNum = dr["e_contactNum"].ToString();
                    p.E_name = dr["e_name"].ToString();
                    p.E_relationship = dr["e_relationship"].ToString();
                    p.FirstName = dr["FirstName"].ToString();
                    p.LastName = dr["LastName"].ToString();
                    p.Postcode = dr["Postcode"].ToString();
                    p.E_address1 = dr["e_address1"].ToString();
                    p.E_address2 = dr["e_address2"].ToString();
                    p.E_postalcode = dr["e_postalcode"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                    }
                    catch (Exception) { }
                    p.Email = dr["email"].ToString();
                    p.Gender = dr["gender"].ToString();
                    p.ContactNumHome = dr["contactNumHome"].ToString();
                    p.Title = dr["title"].ToString();
                    p.ContactNumWork = dr["contactNumWork"].ToString();
                    p.Country = dr["country"].ToString();
                    try
                    {
                        p.PlaceID = Convert.ToInt16(dr["placeID"].ToString());
                    }
                    catch (Exception) { }
                    result.Add(p);
                }
                dr.Close(); dr.Dispose();
                CloseConnection();
                if (result.Count != 0)
                {
                    foreach (patient p in result)
                    {
                        p.ImagePath = this.retrievePatient_faceImg(p.Uid.ToString());
                        p.PlaceName = this.retrieve_place(p.PlaceID);
                    }
                }
            }
            catch (SqlException)
            {
                return null;
            }
            return result;
        }

        public List<patient> retrieveAllOthPatients(string docname)
        {
            List<patient> result = new List<patient>();
            List<patient> newresult = new List<patient>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM patient where doctor <> @doc";
                comm.Parameters.AddWithValue("@doc", docname);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    patient p = new patient();
                    p.Address1 = dr["Address1"].ToString();
                    p.Address2 = dr["Address2"].ToString();
                    p.ContactNum = dr["contactNum"].ToString();
                    p.Doc = dr["doctor"].ToString();
                    p.E_contactNum = dr["e_contactNum"].ToString();
                    p.E_name = dr["e_name"].ToString();
                    p.E_relationship = dr["e_relationship"].ToString();
                    p.FirstName = dr["FirstName"].ToString();
                    p.LastName = dr["LastName"].ToString();
                    p.Postcode = dr["Postcode"].ToString();
                    p.E_address1 = dr["e_address1"].ToString();
                    p.E_address2 = dr["e_address2"].ToString();
                    p.E_postalcode = dr["e_postalcode"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                    }
                    catch (Exception) { }
                    try
                    {
                        p.PlaceID = Convert.ToInt16(dr["placeID"].ToString());
                    }
                    catch (Exception) { }
                    p.Email = dr["email"].ToString();
                    p.Gender = dr["gender"].ToString();
                    p.ContactNumHome = dr["contactNumHome"].ToString();
                    p.Title = dr["title"].ToString();
                    p.ContactNumWork = dr["contactNumWork"].ToString();
                    p.Country = dr["country"].ToString();
                    result.Add(p);
                }
                dr.Close(); dr.Dispose();
                CloseConnection();
                if (result.Count != 0)
                {
                    foreach (patient p in result)
                    {
                        p.ImagePath = this.retrievePatient_faceImg(p.Uid.ToString());
                        p.PlaceName = this.retrieve_place(p.PlaceID);
                    }
                }
            }
            catch (SqlException)
            {
                return null;
            }
            return result;
        }

        public int retrieveNumofOwnPatients(string docname)
        {
            int count = 0;

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "select count(uid) as count from patient where doctor = @doc";
            comm.Parameters.AddWithValue("@doc", docname);
            //count = comm.ExecuteNonQuery();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                count = Convert.ToInt32(dr["count"].ToString());
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return count;
        }

        public int retrieveNumofOthPatients(string docname)
        {
            int count = 0;

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "select count(uid) as count from patient where doctor <> @doc";
            comm.Parameters.AddWithValue("@doc", docname);
            //count = comm.ExecuteNonQuery();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                count = Convert.ToInt32(dr["count"].ToString());
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return count;
        }

        public patient retrievePatient(int uid)
        {
            patient p = new patient();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            //try
            //{
            comm.CommandText = "SELECT * FROM patient where uid = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                //patient p = new patient();
                p.Address1 = dr["Address1"].ToString();
                p.Address2 = dr["Address2"].ToString();
                p.ContactNum = dr["contactNum"].ToString();
                p.Doc = dr["doctor"].ToString();
                p.E_contactNum = dr["e_contactNum"].ToString();
                p.E_name = dr["e_name"].ToString();
                p.E_relationship = dr["e_relationship"].ToString();
                p.FirstName = dr["FirstName"].ToString();
                p.LastName = dr["LastName"].ToString();
                p.Postcode = dr["Postcode"].ToString();
                p.E_address1 = dr["e_address1"].ToString();
                p.E_address2 = dr["e_address2"].ToString();
                p.E_postalcode = dr["e_postalcode"].ToString();
                try
                {
                    p.PlaceID = Convert.ToInt16(dr["placeID"].ToString());
                }
                catch (Exception) { }
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                }
                catch (Exception) { }
                p.Email = dr["email"].ToString();
                p.Gender = dr["gender"].ToString();
                p.ContactNumHome = dr["contactNumHome"].ToString();
                p.Title = dr["title"].ToString();
                p.ContactNumWork = dr["contactNumWork"].ToString();
                p.Country = dr["country"].ToString();

            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            //if (result.Count != 0)
            //{
            //    foreach (patient p in result)
            //    {
            p.ImagePath = this.retrievePatient_faceImg(p.Uid.ToString());
            p.PlaceName = this.retrieve_place(p.PlaceID);
            //    }
            //}
            //}

            return p;
        }

        #endregion

        # region CRUD for gamePlay

        public bool createNewGamePlay(int sessionID, int stageID, string joint, string specialInstructions, string gameKey, string gameValue)
        {
            DateTime dateofplay = DateTime.Now;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            if (stageID == 0)
            {
                comm.CommandText = "INSERT INTO gamePlay(sessionID,specialInstructions,joint,gameKey,gameValue) Values (@sessionID , @specialInstructions, @joint, @gameKey, @gameValue)";
                comm.Parameters.AddWithValue("@sessionID", sessionID);
                comm.Parameters.AddWithValue("@specialInstructions", specialInstructions);
                comm.Parameters.AddWithValue("@joint", joint);
                comm.Parameters.AddWithValue("@gameKey", gameKey);
                comm.Parameters.AddWithValue("@gameValue", gameValue);
            }
            else
            {
                comm.CommandText = "INSERT INTO gamePlay(sessionID, stageID,joint,specialInstructions,gameKey,gameValue) Values (@sessionID , @stageID , @joint, @specialInstructions, @gameKey, @gameValue)";
                comm.Parameters.AddWithValue("@sessionID", sessionID);
                comm.Parameters.AddWithValue("@stageID", stageID);
                comm.Parameters.AddWithValue("@specialInstructions", specialInstructions);
                comm.Parameters.AddWithValue("@joint", joint);
                comm.Parameters.AddWithValue("@gameKey", gameKey);
                comm.Parameters.AddWithValue("@gameValue", gameValue);
            }
            int result = comm.ExecuteNonQuery();
            CloseConnection();
            return result == 0 ? false : true;
        }

        public List<gamePlay> retrieveAllGamePlay()
        {
            List<gamePlay> result = new List<gamePlay>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  gamePlay";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay p = new gamePlay();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.StageID = Convert.ToInt16(dr["stageID"].ToString());
                }
                catch (Exception) { }
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.SpecialInstructions = dr["specialInstructions"].ToString();
                p.Joint = dr["joint"].ToString();
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        public List<gamePlay> retrieveAllGamePlayofSession(int sessionID)
        {
            List<gamePlay> result = new List<gamePlay>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT gamePlay.uid, gameplay.sessionID, gameplay.stageID,gameplay.specialInstructions, gameplay.joint, gameplay.gameKey,gameplay.gameValue,gameplay.other1,gameplay.other2,gameplay.other3,gameplay.other4,gameplay.other5, stage.name FROM  gamePlay, stage WHERE gamePlay.sessionID = @sessionID AND stage.uid = gameplay.stageID";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            //if (conn.State == ConnectionState.Open)
            //{
            //    //SqlDataReader dr = comm.ExecuteReader();
            //}
            //else
            //{
            //    conn.Open();
              
            //}
            SqlDataReader dr = comm.ExecuteReader();
            //SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay p = new gamePlay();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    if (Convert.ToInt16(dr["stageID"].ToString()) != -1)
                    {
                        p.StageID = Convert.ToInt16(dr["stageID"].ToString());
                        p.StageName = dr["name"].ToString();
                    }
                    else
                    {
                        p.StageName = "nostage";
                    }
                   
                }
                catch (Exception)
                {
                    p.SessionID = -1;
                }
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.SpecialInstructions = dr["specialInstructions"].ToString();
                p.Joint = dr["joint"].ToString();
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();
                
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            //foreach (gamePlay gp in result)
            //{
            //    if (gp.StageID == -1)
            //    {
            //        gp.StageName = retrieve_stageName(gp.StageID);
            //    }
            //}
            return result;
        }

        public List<gamePlay> retrieveAllGamePlayofSessionAndStage(int sessionID, int stageID)
        {
            List<gamePlay> result = new List<gamePlay>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  gamePlay WHERE sessionID = @sessionID AND stageID = @stageID";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            comm.Parameters.AddWithValue("@stageID", stageID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay p = new gamePlay();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.StageID = Convert.ToInt16(dr["stageID"].ToString());
                }
                catch (Exception) { }
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.SpecialInstructions = dr["specialInstructions"].ToString();
                p.Joint = dr["joint"].ToString();
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        public gamePlay retrieveGamePlay(int gamePlayID)
        {
            gamePlay p = new gamePlay();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  gamePlay WHERE uid = @uid";
            comm.Parameters.AddWithValue("@uid", gamePlayID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.StageID = Convert.ToInt16(dr["stageID"].ToString());
                }
                catch (Exception) { }
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.SpecialInstructions = dr["specialInstructions"].ToString();
                p.Joint = dr["joint"].ToString();
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return p;
        }

        public List<KeyValuePair<string, string>> getOldestUID(int exeID, int patientID)
        {
            List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
            KeyValuePair<string, string> temp = new KeyValuePair<string, string>();

            //if (uid != 0)
            //{
            SqlOpenConnection();
            string custKey;
            string custValue;
            SqlCommand comm = SqlCommandConnection();
            //comm.CommandText = "Select gameKey, gameValue from gamePlay where sessionID=(Select top 1 sessionID from session where exeID=@exeID and patientID=@patientID and dateofplay=(select min(dateofplay) from session where exeID=@exeID and patientID=@patientID))";
            comm.CommandText = "Select gameKey, gameValue from gamePlay where sessionID=(Select top 1 sessionID from session where  exeID=@exeID and patientID=@patientID order by dateofplay asc)";
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                custKey = dr["gameKey"].ToString();
                custValue = dr["gameValue"].ToString();
                if (custKey.Contains("Average Time"))
                {
                    temp = new KeyValuePair<string, string>(custKey, custValue);
                    tempList.Add(temp);
                }
            }

            dr.Close();
            dr.Dispose();
            CloseConnection();
            //}
            return tempList;
        }

        //public List<gamePlay> retrievePatientAllGamePlay(int patientID)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT * FROM  gamePlay, exe WHERE exe.uid = gamePlay.exeID AND gamePlay.patientID = @patientID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();
        //        if (p.ExeID != 1)
        //        {
        //            p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //            p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //            p.Name = dr["name"].ToString();
        //            try
        //            {
        //                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //                p.Points = dr["points"].ToString();
        //                p.Rounttime = dr["roundtime"].ToString();
        //            }
        //            catch (Exception) { }
        //            p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //            p.SpecialInstructions = dr["specialInstructions"].ToString();
        //            p.AvgTime1 = dr["avgTime1"].ToString();
        //            p.AvgTime2 = dr["avgTime2"].ToString();
        //            p.AvgTime3 = dr["avgTime3"].ToString();
        //            p.AvgTime4 = dr["avgTIme4"].ToString();
        //            result.Add(p);
        //        }
        //    }
        //    CloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientJointGamePlay(int patientID, int joint)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT * FROM  gamePlay, exe, exe_tag WHERE exe.uid = gamePlay.exeID AND exe_tag.exeID = exe.uid AND exe_tag.joint = @joint AND gamePlay.patientID = @patientID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@joint", joint);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();

        //        p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //        p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //        p.Name = dr["name"].ToString();
        //        try
        //        {
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //        }
        //        catch (Exception) { }
        //        p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        p.SpecialInstructions = dr["specialInstructions"].ToString();
        //        p.AvgTime1 = dr["avgTime1"].ToString();
        //        p.AvgTime2 = dr["avgTime2"].ToString();
        //        p.AvgTime3 = dr["avgTime3"].ToString();
        //        p.AvgTime4 = dr["avgTIme4"].ToString();
        //        result.Add(p);

        //    }
        //    CloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientJointGamePlayByEXE(int patientID, int joint, int exeID)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT * FROM  gamePlay, exe, exe_tag WHERE exe.uid = gamePlay.exeID AND exe_tag.exeID = exe.uid AND exe_tag.joint = @joint AND gamePlay.patientID = @patientID  AND exe.uid = @exeID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@joint", joint);
        //    comm.Parameters.AddWithValue("@exeID", exeID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();

        //        p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //        p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //        p.Name = dr["name"].ToString();
        //        try
        //        {
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //        }
        //        catch (Exception) { }
        //        p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        p.SpecialInstructions = dr["specialInstructions"].ToString();
        //        p.AvgTime1 = dr["avgTime1"].ToString();
        //        p.AvgTime2 = dr["avgTime2"].ToString();
        //        p.AvgTime3 = dr["avgTime3"].ToString();
        //        p.AvgTime4 = dr["avgTIme4"].ToString();
        //        result.Add(p);

        //    }
        //    CloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientMostRecentGamePlay(int patientID, int number)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT TOP @number * FROM  gamePlay, exe WHERE exe.uid = gamePlay.exeID AND gamePlay.patientID = @patientID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@numer", number);
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();
        //        if (p.ExeID != 1)
        //        {
        //            p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //            p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //            p.Name = dr["name"].ToString();
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //            p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //            p.SpecialInstructions = dr["specialInstructions"].ToString();
        //            p.Anglemax = dr["anglemax"].ToString();
        //            p.Anglemin = dr["anglemin"].ToString();
        //            result.Add(p);
        //        };
        //    }
        //    CloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientGamePlayofaEXE(int patientID, int exeID)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT * FROM  gamePlay WHERE patientID = @patientID AND exeID = @exeID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@exeID", exeID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();
        //        p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //        p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //        try
        //        {
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //        }
        //        catch (Exception) { }
        //        p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        p.Anglemax = dr["anglemax"].ToString();
        //        p.Anglemin = dr["anglemin"].ToString();
        //        p.Joint = dr["joint"].ToString();
        //        p.AvgTime1 = dr["avgTime1"].ToString();
        //        p.AvgTime2 = dr["avgTime2"].ToString();
        //        p.AvgTime3 = dr["avgTime3"].ToString();
        //        p.AvgTime4 = dr["avgTIme4"].ToString();
        //        result.Add(p);
        //    }
        //    CloseConnection();
        //    return result;
        //}

        #endregion

        # region CRUD for Tag
        public string retrieveJointName(string joint)
        {
            string result = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT jointName FROM  tag WHERE joint = @joint ";
            comm.Parameters.AddWithValue("@joint", joint);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = dr["jointName"].ToString();
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }
        # endregion

        # region CRUD for exeType

        public string retrieveExeTypeDescription(int exeType)
        {
            string result = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT description FROM  exeType WHERE uid = @exeType ";
            comm.Parameters.AddWithValue("@exeType", exeType);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = dr["description"].ToString();
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        #endregion

        #region CRUD for patient_exe

        public int retrieveExeIDAssignedtoPatient(int exe, int patient, DateTime dateassigned)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT uid FROM patient_exe WHERE exe = @exe AND  patient = @patient AND dateassigned = @dateassigned";
            comm.Parameters.AddWithValue("@exe", exe);
            comm.Parameters.AddWithValue("@patient", patient);
            comm.Parameters.AddWithValue("@dateassigned", dateassigned);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToInt16(dr["uid"].ToString());
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        public List<EXE> retrieveExeAssignedtoPatient(int patientID)
        {
            List<EXE> result = new List<EXE>();
            List<EXE> result1 = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT patient_exe.uid, patient_exe.dateassigned, exe.path, exe.description, exe.uid AS exeID, exe.name FROM  patient_exe, exe WHERE patient_exe.patient = @patientID AND patient_exe.exe = exe.uid ORDER BY dateassigned DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                EXE temp = new EXE();
                temp.Description = dr["description"].ToString();
                temp.Path = dr["path"].ToString();
                temp.Uid = Convert.ToInt16(dr["exeID"].ToString());
                temp.Name = dr["name"].ToString();
                try
                {
                    temp.Patient_exe_id = Convert.ToInt16(dr["uid"].ToString());
                }
                catch (Exception) { }
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();   
            CloseConnection();
            foreach (EXE temp in result)
            {
                temp.Tag = retrieveTag(temp.Uid);
                temp.CustKey = retrieve_exe_custKey(temp.Uid);
                temp.SettingsList = retrieve_patient_exe_custKey(temp.Patient_exe_id);
                result1.Add(temp);
            }
            return result1;
        }

        public bool deleteExeAssignedtoPatient(int uid)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "DELETE FROM patient_exe WHERE patient = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            int count = comm.ExecuteNonQuery();
            CloseConnection();
            return count == 0 ? false : true;
        }

        public bool createExeAssingedtoPatient(int patientID, List<EXE> exeList)
        {
            DateTime dateofplay = DateTime.Now;
            int count = 0;
            foreach (EXE exe in exeList)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO patient_exe(exe, patient, dateassigned) VALUES(@exeID, @patientID, @dateofplay)";
                comm.Parameters.AddWithValue("@exeID", exe.Uid);
                comm.Parameters.AddWithValue("@patientID", patientID);
                comm.Parameters.AddWithValue("@dateofplay", dateofplay);
                count = comm.ExecuteNonQuery();
                CloseConnection();

                int patient_exe_id = retrieveExeIDAssignedtoPatient(exe.Uid, patientID, dateofplay);
                createPatientExeCustkey(exe.SettingsList, patient_exe_id);
            }

            return count == 0 ? false : true;
        }

        # endregion

        # region CRUD for session

        //NOT MIGHT WORK
        public int createNewSession(int patientID, int exeID)
        {
            DateTime dateofplay = DateTime.Now;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO session(patientID,exeID,dateofplay) Values (@patientID , @exeID , @dateofplay)";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@dateofplay", dateofplay);
            comm.ExecuteNonQuery();
            CloseConnection();
            int result = retrieveMostRecentSessionIDOfPatientofExe(patientID, exeID);
            return result;
        }

        public int retrieveMostRecentSessionIDOfPatientofExe(int patientID, int exeID)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT TOP 1 * FROM  session WHERE patientID = @patientID AND exeID = @exeID ORDER BY dateofplay DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToInt16(dr["sessionID"].ToString());

            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        //retrieve session base on patient
        public List<session> retrieveSessionOfPatient(int patientID)
        {
            List<session> result = new List<session>();
            List<session> result1 = new List<session>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  session WHERE patientID = @patientID ";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                session p = new session();
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
                p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            //Parallel.ForEach(result, s =>
            foreach (session s in result)
            {
                s.GamePlaylist = retrieveAllGamePlayofSession(s.SessionID);
                result1.Add(s);
            }
            //); 
            return result1;
        }

        //retrieve session base on exe and patient
        public List<session> retrieveSessionOfPatientofExe(int patientID, int exeID)
        {
            List<session> result = new List<session>();
            List<session> result1 = new List<session>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  session WHERE patientID = @patientID AND exeID = @exeID ";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                session p = new session();
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
                p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            foreach (session s in result)
            {
                s.GamePlaylist = retrieveAllGamePlayofSession(s.SessionID);
                result1.Add(s);
            }
            return result1;
        }

        public List<session> retrieveSession(int sessionID)
        {
            List<session> result = new List<session>();
            List<session> result1 = new List<session>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  session WHERE sessionID = @sessionID ";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                session p = new session();
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
                p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            foreach (session s in result)
            {
                s.GamePlaylist = retrieveAllGamePlayofSession(s.SessionID);
                result1.Add(s);
            }
            return result1;
        }

        // 1 big session has many gameplay
        //public List<EXE> retrieveExeAssignedtoPatient(int patientID)
        //{
        //    List<EXE> result = new List<EXE>();
        //    List<EXE> result1 = new List<EXE>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT * FROM  patient_exe, exe WHERE patient_exe.patient = @patientID AND patient_exe.exe = exe.uid";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        EXE temp = new EXE();
        //        temp.Description = dr["description"].ToString();
        //        temp.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        temp.Name = dr["name"].ToString();
        //        result.Add(temp);
        //    }
        //    CloseConnection();
        //    foreach (EXE temp in result)
        //    {
        //        temp.Tag = retrieveTag(temp.Uid);
        //        result1.Add(temp);
        //    }
        //    return result1;
        //}

        #endregion

        # region CRUD for exe_stage

        public List<stage> retrieveExeStage(int exeID)
        {
            List<stage> result = new List<stage>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exe_stage.stage,stage.name FROM exe_stage,stage WHERE exe_stage.exe = @exeID AND stage.uid = exe_stage.stage";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                stage temp = new stage();
                temp.Name = dr["name"].ToString();
                temp.Uid = Convert.ToInt16(dr["stage"].ToString());
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return result;
        }

        #endregion

        #region retrieve_exe_custKey

        public void create_exe_custKey(List<string> custKey, int exe)
        {
            foreach (string temp in custKey)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO exe_custKey Values (@exe ,@custKey)";
                comm.Parameters.AddWithValue("@exe", exe);
                comm.Parameters.AddWithValue("@custKey", temp);
                CloseConnection();
            }
        }

        public List<string> retrieve_exe_custKey(int exeID)
        {
            List<string> result = new List<string>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT custKey FROM exe_custKey WHERE exe = @exeID";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result.Add((dr["custKey"].ToString()));
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }

        #endregion

        #region retrieve_patient_exe_custKey

        public bool createPatientExeCustkey(List<KeyValuePair<string, string>> settingsList, int patient_exe_id)
        {
            int count = 0;
            foreach (KeyValuePair<string, string> temp in settingsList)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO patient_exe_custKey VALUES(@patient_exe_id, @custKey, @custValue)";
                comm.Parameters.AddWithValue("@patient_exe_id", patient_exe_id);
                comm.Parameters.AddWithValue("@custKey", temp.Key.ToString());
                comm.Parameters.AddWithValue("@custValue", temp.Value.ToString());
                count = Convert.ToInt16(comm.ExecuteNonQuery());
                CloseConnection();
            }

            return count == 0 ? false : true;
        }

        public List<KeyValuePair<string, string>> retrieve_patient_exe_custKey(int patient_exe_id)
        {
            List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT custKey, custValue FROM patient_exe_custKey WHERE patient_exe_id = @patient_exe_id";
            comm.Parameters.AddWithValue("@patient_exe_id", patient_exe_id);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                KeyValuePair<string, string> temp = new KeyValuePair<string, string>(dr["custKey"].ToString(), dr["custValue"].ToString());
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }

        #endregion

        # region CRUD stage

        public string retrieve_stageName(int stageID)
        {
            string result = "";

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT name FROM stage WHERE uid = @stageID";
            comm.Parameters.AddWithValue("@stageID", stageID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = ((dr["name"].ToString()));
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }

        #endregion

        #region generate report

        public string retrieveUserNotes(string joint, int patientID, int exeID)
        {

            string result = "";

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT TOP 1 specialInstructions FROM session, gamePlay WHERE session.sessionID = gamePlay.sessionID AND session.exeID = @exeID AND session.patientID = @patientID AND gamePlay.joint = @joint ORDER BY session.dateofplay DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@joint", joint);
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = ((dr["specialInstructions"].ToString()));
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }

        public List<gamePlay> generateNotes(string joint, int patientID, int exeID)
        {
            List<gamePlay> result = new List<gamePlay>();
            int sessionID = retrieveMostRecentSessionIDOfPatientofExe(patientID, exeID);
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM gamePlay WHERE joint = @joint AND sessionID = @sessionID";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            comm.Parameters.AddWithValue("@joint", joint);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay gp = new gamePlay();
                gp.GameKey = dr["gameKey"].ToString();
                gp.GameValue = dr["gameValue"].ToString();
                gp.Joint = dr["joint"].ToString();
                gp.Uid = Convert.ToInt16(dr["uid"].ToString());
                result.Add(gp);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }


        #endregion

        #region CRUD place

        public List<place> getPlace()
        {
            List<place> placeList = new List<place>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();

            comm.CommandText = "SELECT * FROM place";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                place p = new place();
                p.Uid = Convert.ToInt32(dr["uid"].ToString());
                p.Name = dr["name"].ToString();
                placeList.Add(p);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return placeList;
        }

        public string retrieve_place(int placeID)
        {
            string result = "";

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT name FROM place WHERE uid = @placeID";
            comm.Parameters.AddWithValue("@placeID", placeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = ((dr["name"].ToString()));
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }

        #endregion

        #region score
        #region personal score
        public int retrievePersonalHighScore(int patientID, int exeID, int difficulty)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select TOP 1 gameValue from gamePlay WHERE gamekey = 'Score (Points)' AND sessionID in " +
            "(SELECT sessionID FROM gamePlay WHERE gameKey = 'Difficulty (1-10)' " +
            "and gameValue = @difficulty " +
            "and sessionID in (SELECT sessionID FROM session " +
            "WHERE patientID = @patientID AND exeID = @exeID)) order by gameValue desc";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@difficulty", difficulty);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToInt16(dr[0].ToString());
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }

        //public int retrievePersonalScore(int sessionID)
        //{
        //    int result = 0;
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT gamePlay.gameValue from gamePlay WHERE gamePlay.sessionID = @sessionID and gamePlay.gameKey = @gameKey";
        //    comm.Parameters.AddWithValue("@sessionID", sessionID);
        //    comm.Parameters.AddWithValue("@gameKey", "Score (Points)");
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        result = Convert.ToInt32(dr["gameValue"].ToString());
        //    }

        //    return result;
        //}

        public int retrieveSessionIDFromDifficulty(int sessionID, int difficulty)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT sessionID FROM gamePlay WHERE gameKey = @gameKey and gameValue = @difficulty and sessionID = @sessionID";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            comm.Parameters.AddWithValue("@difficulty", difficulty);
            comm.Parameters.AddWithValue("@gameKey", "Difficulty (1-10)");
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                int ID = Convert.ToInt16(dr["sessionID"].ToString());
                result = ID;
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return result;
        }
        #endregion

        //#region leaderboard
        //public List<highScore> retrieveHighScore(int exeID, int difficulty)
        //{
        //    #region get sessionID from session table according to exeID
        //    List<highScore> resultUID = new List<highScore>();
        //    //List<int> result1 = new List<int>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT sessionID FROM session WHERE exeID = @exeID ";
        //    comm.Parameters.AddWithValue("@exeID", exeID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        highScore h = new highScore();
        //        h.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
        //        h.PatientId = Convert.ToInt16(dr["patientID"].ToString());
        //        resultUID.Add(h);
        //    }
        //    dr.Close(); dr.Dispose();
        //    CloseConnection();
        //    #endregion

        //    List<int> finalUID = new List<int>();
        //    foreach (highScore i in resultUID)
        //    {
        //        finalUID.Add(retrieveSessionIDFromDifficulty(i.SessionID, difficulty));
        //    }

        //    List<highScore> result1 = new List<highScore>();
        //    foreach (int i in finalUID)
        //   { 
        //        highScore h = new highScore();
        //        h.Score = retrieveScore(i);
        //        h.SessionID = i;
        //        result1.Add(h);
        //    }

        //    foreach (highScore i in result1)
        //    {
        //        i.PatientId = retrievePatientIDUsingSessionID(i.SessionID);
        //    }

        //    var toReturn = from n in result1
        //                   group n by n.PatientId into g
        //                   select new { PatientId = g.Key, Score = g.Max(n => n.Score) };

        //    List<highScore> returnList = new List<highScore>();
        //    foreach (var i in toReturn)
        //    {
        //        highScore h = new highScore();
        //        h.PatientId = i.PatientId;
        //        h.Score = i.Score;
        //        returnList.Add(h);
        //    }

        //    return returnList;
        //}

        //public int retrievePatientIDUsingSessionID(int sessionID)
        //{
        //    int result = 0;
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT patientID FROM session WHERE sessionID = @sessionID";
        //    comm.Parameters.AddWithValue("@sessionID", sessionID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        int ID = Convert.ToInt16(dr["patientID"].ToString());
        //        result = ID;
        //    }
        //    dr.Close(); dr.Dispose();
        //    CloseConnection();

        //    return result;
        //}

        //public int retrieveScore(int sessionID)
        //{
        //    int result = 0;
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT gameValue FROM  gamePlay WHERE gameKey = @gameKey and sessionID = @sessionID";
        //    comm.Parameters.AddWithValue("@sessionID", sessionID);
        //    comm.Parameters.AddWithValue("@gameKey", "Score (Points)");
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        int ID = Convert.ToInt16(dr["gameValue"].ToString());
        //        result = ID;
        //    }
        //    dr.Close(); dr.Dispose();
        //    CloseConnection();

        //    return result;
        //}
        //#endregion

        public class resultDB
        {
            int sessionID;

            public int SessionID
            {
                get { return sessionID; }
                set { sessionID = value; }
            }

            int score;

            public int Score
            {
                get { return score; }
                set { score = value; }
            }
        }

        public List<highScore> retrieveHighScore(int exeID, int difficulty)
        {
            //get SessionID and gameValue
            List<highScore> resultUID = new List<highScore>();
            ArrayList nameList = new ArrayList();
            ArrayList list = getTopPlayers(difficulty, exeID);
            foreach (resultDB rdb in list)
            {
                nameList.Add(getPlayerNames(rdb.SessionID));
            }
            if (list.Count == nameList.Count)
            {
                for (int i = 0; i < nameList.Count; i++)
                {
                    highScore h = new highScore();
                    h.Name = nameList[i].ToString();
                    resultDB rdb = list[i] as resultDB;
                    h.Score = rdb.Score;
                    resultUID.Add(h);
                }
            }
            else
            { }
            return resultUID;
        }
        public string getPlayerNames(int rdb)
        {
            string name = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select FirstName + LastName From patient WHERE uid in " +
            "(Select patientID from session WHERE sessionID = @sessionID)";
            comm.Parameters.AddWithValue("@sessionID", rdb);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                name = ((dr[0].ToString()));
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
            return name;
        }
        public ArrayList getTopPlayers(int difficulty, int exeID)
		{
			ArrayList al = new ArrayList();
			
			SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "(Select Top 10 sessionID,gameValue  from gamePlay WHERE gamekey = 'Score (Points)' AND sessionID in "+
			"(SELECT sessionID FROM gamePlay WHERE gameKey = 'Difficulty (1-10)' "+
			"and gameValue =  @difficulty " +
			"and sessionID in (SELECT sessionID FROM session "+
			"WHERE exeID = @exeID))) order by gameValue desc";
            comm.Parameters.AddWithValue("@exeID", exeID);
			comm.Parameters.AddWithValue("@difficulty", difficulty);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                resultDB rdb = new resultDB();
                 rdb.SessionID = Convert.ToInt16(dr[0].ToString());
				 rdb.Score = Convert.ToInt16(dr[1].ToString());
				 al.Add(rdb);
            }
            dr.Close(); dr.Dispose();
            CloseConnection();
			return al;
		}
        #endregion

        #region getDoctor
        /// <summary>
        /// Not using at the moment. for ddl in patients.aspx
        /// </summary>
        /// <returns></returns>
        public List<string> getDoctor()
        {
            List<string> docList = new List<string>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT userType FROM user_roles, roles WHERE user_roles.roles = roles.uid";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                docList.Add(dr["userType"].ToString());
            }
            dr.Close(); dr.Dispose();
            CloseConnection();

            return docList;
        }
        #endregion

        #region teleRehab
        public string loginPatient(string username, string password)
        {
            SqlOpenConnection();
            string name = "";
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select name from UserAccount where email=@username AND password=@password";
            comm.Parameters.AddWithValue("@username", username);
            comm.Parameters.AddWithValue("@password", hashPassword(password));

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                name = (string)(dr["name"]);
            }
            dr.Close();
            dr.Dispose();
            CloseConnection();
            return name;
        }

        public int getPatientUID(string email)
        {
            SqlOpenConnection();
            int uid = 0;
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select uid from patient where email=@email";
            comm.Parameters.AddWithValue("@email", email);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                uid = Convert.ToInt32(dr["uid"].ToString());
            }
            dr.Close();
            dr.Dispose();
            CloseConnection();

            return uid;
        }
        
        //public List<KeyValuePair<string, string>> getLatestUID(int exeID, int patientID)
        //{
        //    SqlOpenConnection();
        //    int uid = 0;
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "Select top 1 uid from patient_exe where exe=@exeID and patient=@patientID and dateassigned=(select max(dateassigned) from patient_exe where exe=@exeID and patient=@patientID)";
        //    comm.Parameters.AddWithValue("@exeID", exeID);
        //    comm.Parameters.AddWithValue("@patientID", patientID);

        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        uid = Convert.ToInt32(dr["uid"].ToString());
        //    }
        //    dr.Close();
        //    dr.Dispose();
        //    CloseConnection();
        //    List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
        //    KeyValuePair<string, string> temp = new KeyValuePair<string, string>();

        //    if (uid != 0)
        //    {
        //        SqlOpenConnection();
        //        string custKey;
        //        string custValue;
        //        comm = SqlCommandConnection();
        //        comm.CommandText = "Select custKey, custValue from patient_exe_custKey where patient_exe_id=@uid";
        //        comm.Parameters.AddWithValue("@uid", uid);

        //        dr = comm.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            custKey = dr["custKey"].ToString();
        //            custValue = dr["custValue"].ToString();
        //            temp = new KeyValuePair<string, string>(custKey, custValue);
        //            tempList.Add(temp);
        //        }

        //        dr.Close();
        //        dr.Dispose();
        //        CloseConnection();
        //    }
        //    return tempList;
        //}

        public List<KeyValuePair<string, string>> getLatestUID(int exeID, int patientID)
        {
            List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
            KeyValuePair<string, string> temp = new KeyValuePair<string, string>();
            SqlOpenConnection();
            string custKey;
            string custValue;
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select custKey, custValue from patient_exe_custKey where patient_exe_id=(Select top 1 uid from patient_exe where exe=@exeID and patient=@patientID and dateassigned=(select max(dateassigned) from patient_exe where exe=@exeID and patient=@patientID))";
            //comm.Parameters.AddWithValue("@uid", uid);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                custKey = dr["custKey"].ToString();
                custValue = dr["custValue"].ToString();
                temp = new KeyValuePair<string, string>(custKey, custValue);
                tempList.Add(temp);
            }
            dr.Close();
            dr.Dispose();
            CloseConnection();
            return tempList;
        }

        public List<double> getLatestAngle(int exeID, int patientID, int jointID, string axis)
        {
            List<double> value = new List<double>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select sessionID from session where sessionid = (select max(sessionid) from session where patientid=@patientID and exeID=@exeID)";
            //int sessionid = 0;
            //comm.Parameters.AddWithValue("@uid", uid);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);
            int sessionid = 0;
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                sessionid = Convert.ToInt32(dr["sessionID"].ToString());
                //temp = new KeyValuePair<string, string>(custKey, custValue);
                //tempList.Add(temp);
            }
            dr.Close();
            comm.CommandText = "select gameValue from gameplay where sessionID = @sessionID and joint = @jointID and gamekey = @gameKey";
            comm.Parameters.AddWithValue("@sessionID", sessionid);
            comm.Parameters.AddWithValue("@jointID", jointID);
            comm.Parameters.AddWithValue("@gameKey", ("Max Angle " + axis).Trim());

            double maxValue = 0.0;
            dr = comm.ExecuteReader();
            while (dr.Read())
            {
                maxValue = Convert.ToInt32(dr["gameValue"].ToString());
                //temp = new KeyValuePair<string, string>(custKey, custValue);
                //tempList.Add(temp);
            }

            dr.Close();
            comm.Parameters.Clear();
            comm.CommandText = "select gameValue from gameplay where sessionID = @sessionID and joint = @jointID and gamekey = @gameKey";
            comm.Parameters.AddWithValue("@sessionID", sessionid);
            comm.Parameters.AddWithValue("@jointID", jointID); 
            comm.Parameters.AddWithValue("@gameKey", ("Min Angle " + axis).Trim());
            double minValue = 0.0;
            dr = comm.ExecuteReader();
            while (dr.Read())
            {
                minValue = Convert.ToInt32(dr["gameValue"].ToString());
                //temp = new KeyValuePair<string, string>(custKey, custValue);
                //tempList.Add(temp);
            }

            value.Add(maxValue);
            value.Add(minValue);
            dr.Dispose();
            CloseConnection();


            return value;
        }

        #endregion

        #region web usage
        public string[] siteLoginPatient(string username, string password)
        {
            string [] result = new string[2];
            SqlOpenConnection();
            string name = "";
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select name from UserAccount where email=@username AND password=@password";
            comm.Parameters.AddWithValue("@username", username);
            comm.Parameters.AddWithValue("@password", hashPassword(password));

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                name = (string)(dr["name"]);
                result[0] = name;
            }
            dr.Close();
            dr.Dispose();
            CloseConnection();
            if (name != null)
            {
                SqlOpenConnection();
                string uid = "";
                comm = SqlCommandConnection();
                comm.CommandText = "Select uid from patient where email=@username";
                comm.Parameters.AddWithValue("@username", username);
                dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    uid = (dr["uid"]).ToString();
                    result[1] = uid;
                }
                dr.Close();
                dr.Dispose();
                CloseConnection();
            }
            return result;
        }
        #endregion
    }
}